//
// Created by xy on 2024/10/23.
//

#include "sql.h"
#include "log.h"
#include "public.h"

pthread_mutex_t mysql_mutex = PTHREAD_MUTEX_INITIALIZER;

bool connect_to_db(MYSQL** connection, const char *host, const char *user, const char *password, const char *dbname) {
    pthread_mutex_lock(&mysql_mutex);
    *connection = mysql_init(NULL);  // 使用解引用来修改外部指针
    if (*connection == NULL) {
        ERROR_LOG("mysql_init() failed");
        pthread_mutex_unlock(&mysql_mutex);
        return false;
    }

    if (mysql_real_connect(*connection, host, user, password, dbname, 0, NULL, 0) == NULL) {
        ERROR_LOG("mysql_real_connect() failed: %s", mysql_error(*connection));
        mysql_close(*connection);
        pthread_mutex_unlock(&mysql_mutex);
        return false;
    }

    pthread_mutex_unlock(&mysql_mutex);  // 在连接成功后解锁
    return true;
}

bool execute_non_query(MYSQL* connection, const char *query) {
    if (mysql_query(connection, query)) {
        INFO_LOG("Query failed: %s", mysql_error(connection));
        return false; // 执行失败
    }
    return true; // 执行成功
}

MYSQL_RES* execute_query(MYSQL* connection, const char *query) {
    if (mysql_query(connection, query)) {
        return NULL;
    }

    MYSQL_RES *result = mysql_store_result(connection);
    if (result == NULL) {
    }

    return result;
}

void free_result(MYSQL_RES *result) {
    mysql_free_result(result);
}

void close_db_connection(MYSQL* connection) {
    mysql_close(connection);
}

int getUserID(MYSQL* connection, const char* user){
    char query[SQL_QUERY_SIZE];
    sprintf(query, "SELECT * FROM users WHERE username = '%s'", user);
    MYSQL_RES* result = execute_query(connection, query);
    if(!result){
        DEBUG_LOG("select username = %s failed",user);
        return -1;
    }
    MYSQL_ROW data = mysql_fetch_row(result);
    if (data){
        char* id = data[USER_ID];
        return atoi(id);
    }
    return -1;
}

bool init_mysql(MYSQL **connection) {
    if (!connect_to_db(connection, parseINI("sql_ip", NULL), parseINI("sql_user", NULL),
                       parseINI("sql_pwd", NULL), parseINI("sql_db", NULL))) {
        ERROR_LOG("mssql connection failed");
        return false;
    }
    return true;
}

bool utils_remove_file(MYSQL* connection, const char* user, const char* path) {

    // 根据用户名查询得到 ID
     int user_id = getUserID(connection,user);

  char update_query[SQL_QUERY_SIZE];
  sprintf(update_query, "UPDATE directory_forest SET delete_sign = 1 WHERE belong_user = %d AND file_path = '%s' AND delete_sign = 0", user_id, path);
  bool remove_result = execute_non_query(connection, update_query);
  if (!remove_result) {
    DEBUG_LOG("Failed to update delete_sign for user_id: %d and path: %s", user_id, path);
    return false;
  }

  // 确定文件是否存在
  char query[SQL_QUERY_SIZE];
  sprintf(query, "SELECT * FROM directory_forest WHERE belong_user = %d AND file_path = '%s'",user_id,path);
  MYSQL_RES* result = execute_query(connection, query);
  if(!result){
    DEBUG_LOG("Query failed or result is NULL");
    return false;
  }
  MYSQL_ROW data = mysql_fetch_row(result);

  // 修改标志位
  if (data) {
    memset(query, 0, SQL_QUERY_SIZE);
    char* id = data[FILE_ID];
    if (id) {
      int file_id = atoi(id);
      sprintf(query, "UPDATE directory_forest SET delete_sign = 1 WHERE file_path like '%s%%'", path);
      bool update_result = execute_non_query(connection, query);
      if (!update_result){
        DEBUG_LOG("utils_remove_file UPDATE failed");
      }
      mysql_free_result(result);  // 释放结果集
      return update_result;
    }
  }

  mysql_free_result(result);
  return false;
}

bool utils_logout(MYSQL* connection, const char* user) {
    char query[SQL_QUERY_SIZE];
    snprintf(query, sizeof(query), "UPDATE users SET death=1 WHERE username='%s'", user);
    if (!execute_non_query(connection, query)){
        DEBUG_LOG("User = %s utils_logout UPDATE",user);
        return false;
    }
    return true;
}

bool utils_register(MYSQL* connection, const char* user, const char* pwd) {
    // 查询用户名是否已存在
    if (getUserID(connection,user) >= 0){
        return false;
    }

    //成盐值和哈希密码
    char salt[SALT_LENGTH];
    generate_random_salt(salt);
    char hashed_pwd[HASH_PWD_SIZE];
    hash_password_with_salt(pwd,salt,hashed_pwd);


    // 插入新用户
    char query[SQL_QUERY_SIZE];
    snprintf(query, sizeof(query),
             "INSERT INTO users (username, password, salt, last_active, death) VALUES ('%s', '%s', '%s', NOW(), 0)",
             user, hashed_pwd, salt);

    if (execute_non_query(connection, query)) {
        memset(query,0,SQL_QUERY_SIZE);
        snprintf(query, sizeof(query),
                 "INSERT INTO directory_forest (file_name,belong_user,father_id,file_path ,file_type) VALUES ('%s', %d, %d, '%s', %d)",
                 user,getUserID(connection,user), -1,user,1);

        if(mysql_query(connection,query))
        {
            ERROR_LOG("%s\n",mysql_error(connection));
            return false;
        }

        return true;
    } else {
        INFO_LOG("register failed");
    }
    return false;
}


bool utils_login(MYSQL* connection, const char* user, const char* pwd) {
    // 查询用户名是否已存在
    if (getUserID(connection,user) < 0){
        return false;
    }

    char query[SQL_QUERY_SIZE];
    sprintf(query, "SELECT * FROM users WHERE username = '%s'", user);
    MYSQL_RES* result = execute_query(connection, query);


    MYSQL_ROW data = mysql_fetch_row(result);
    const char* stored_hashed_password = data[PASSWORD];  // 数据库中的哈希密码
    const char* salt = data[SALT];                    // 数据库中的盐值

    char hashed_password[SHA256_DIGEST_LENGTH * 2 + 1];  // 用于存储生成的哈希值
    hash_password_with_salt(pwd, salt, hashed_password);  // 根据传入的密码和数据库盐生成哈希

    bool success = (strcmp(stored_hashed_password, hashed_password) == 0);  // 比较哈希值

    free_result(result);
    return success;
}

bool utils_mkdir(MYSQL *connection,const char *user,const char *path,const char *file_name)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    char *belongUser;
    char *fatherId;


    // 目录名中不能有/
    for(int i = 0;i<strlen(file_name);i++)
        if(file_name[i] == '/')
            return false;

    char sql[1000] = { 0 };

    // 判断该路径是否已经存在
    sprintf(sql,"select\
            exists\
            (select\
             1\
             from directory_forest\
             where file_path = '%s/%s' and file_type = 1 and delete_sign = 0\
            )"\
            ,path,file_name);

    if(mysql_query(connection,sql))
    {
        ERROR_LOG("%s\n",mysql_error(connection));
        return false;
    }

    result = mysql_store_result(connection);
    
    row = mysql_fetch_row(result);

    if(strcmp(row[0],"1") == 0)
    {
        printf("该路径已存在\n");
        DEBUG_LOG("%s\n",mysql_error(connection));
        return false;
    }
    
    mysql_free_result(result);
    // 查询用户id、父目录id
    sprintf(sql,"select \
            u.user_id,d.file_id father_id\
            from directory_forest d\
            inner join users u on d.belong_user = u.user_id\
            where u.username = '%s' and d.file_path = '%s' and file_type = 1 and delete_sign = 0"\
            , user,path);

    if(mysql_query(connection,sql))
    {
        ERROR_LOG("%s\n",mysql_error(connection));
        return false;
    }

    result = mysql_store_result(connection);
    row = mysql_fetch_row(result);

    belongUser = row[0];
    fatherId = row[1];
    mysql_free_result(result);

    // 插入数据库中
    memset(sql,0,sizeof(sql));
    sprintf(sql,"insert into directory_forest \
            (file_name,belong_user,father_id,file_path,file_type,file_hash,transfer_success,delete_sign)\
            values('%s','%s','%s','%s/%s',1,'',0,0)",file_name,belongUser,fatherId,path,file_name);

    if(mysql_query(connection,sql))
    {
        ERROR_LOG("%s\n",mysql_error(connection));
        return false;
    }
    DEBUG_LOG("mkdir执行完成\n");
    return true;

}

int get_file_hash(MYSQL* connection,const char* filename, const char* path,const char* username,char* hash)
{
    MYSQL_RES* ret;
    MYSQL_ROW row;
    char query[SQL_QUERY_SIZE]={0};
    sprintf(query,"select *from directory_forest\
            where file_name='%s'and file_path='%s'\
            and belong_user=(select user_id from users\
                             where username = '%s')",\
            filename,path,username);
    ret= execute_query(connection,query);
    row= mysql_fetch_row(ret);
    memcpy(hash,row[FILE_HASH],strlen(row[FILE_HASH]));
    mysql_free_result(ret);
        return 0;

}

bool utils_cd(MYSQL *connection,const char *user,const char *cur_path,const char *target_dir)
{MYSQL_RES *result;
    MYSQL_ROW row;

    char sql[500] = { 0 };
    char path[100] = { 0 };
    sprintf(path,"%s/%s",cur_path,target_dir);
    sprintf(sql,
            "select exists\
            (select\
             1\
             from directory_forest d\
             inner join users u on d.belong_user = u.user_id\
             where u.username = '%s' and d.file_path = '%s' \
             and d.file_type = 1 and delete_sign = 0)"\
            ,user,path);

    if(mysql_query(connection,sql))
    {
        printf("%s\n",mysql_error(connection));
        return false;
    }

    result = mysql_store_result(connection);

    row = mysql_fetch_row(result);

    if(strcmp(row[0],"0") == 0)
    {
        mysql_free_result(result);
        return false;
    }

    mysql_free_result(result);
    return true;
}


bool utils_puts(MYSQL* connection,const char* user , const char* path , const char* file_name , const char* hash)
{
    char query[SQL_QUERY_SIZE]={0};
    int usr_id =getUserID(connection,user);
    //前文件的父目录ID
    sprintf(query,"select file_id from directory_forest \
            where file_path='%s' and file_type = 1",path);
    //访问数据库
    MYSQL_RES* father=execute_query(connection,query);
    mysql_free_result(father);

    MYSQL_ROW row;
    row=mysql_fetch_row(father);
    int father_id =atoi(row[0]);
    memset(query,0,sizeof(query));

    sprintf(query,"select exists (select 1 from directory_forest where file_name = '%s'\
        and file_path = '%s') ",file_name,path);

   father=execute_query(connection,query);
    
    MYSQL_ROW row1=mysql_fetch_row(father);

    if(atoi(row1[0])==1)
       {
           mysql_free_result(father);
       printf("有重名文件");
       return false;
       }
    //核心sql语句执行
    memset(query,0,sizeof(query));
    sprintf(query,"insert into directory_forest values (NULL,'%s',%d,%d,'%s/%s',2,'%s',0,0)" \
            ,file_name,usr_id,father_id,path,file_name,hash);

    if(!execute_non_query(connection,query))
    {
        ERROR_LOG("%s",mysql_error(connection));
        return false;
    }
    else return true;
}
bool file_is_exist_byhash(MYSQL* connection,const char* hash)	// 判断某个文件是否存在
{
    char query[SQL_QUERY_SIZE]={0};
    sprintf(query,"select exists (SELECT 1 FROM directory_forest WHERE file_hash ='%s'and file_type =2)",hash);
    MYSQL_RES* ret=execute_query(connection,query);
    MYSQL_ROW row = mysql_fetch_row(ret);


    if(atoi(row[0])==0)
    {
        mysql_free_result(ret); 
        return false;
    }
    mysql_free_result(ret); 
    return true;
}

bool file_be_delete(MYSQL* connection,const char * file_name,const char* path)
{
    char query[SQL_QUERY_SIZE]={0};
    sprintf(query," select exists(select 1 from directory_forest \
        where file_name='%s' and file_path='%s'and file_type =2 and delete_sign = 1)",\
            file_name,path);
    MYSQL_RES* ret= execute_query(connection,query);
    MYSQL_ROW row;
    row=mysql_fetch_row(ret);
    if(atoi(row[0])==1)
    {
        mysql_free_result(ret);
        return true;}
    else 
    {
        mysql_free_result(ret);
        return false;}
    return true;
}

bool file_is_exist_byname(MYSQL* connection,const char* file_name ,const char* path)
{
    char query[SQL_QUERY_SIZE]={0};
    sprintf(query," select exists (select 1 from directory_forest where \
        file_name='%s' and file_path='%s'and file_type =2 and delete_sign = 0)",\
            file_name,path);
    MYSQL_RES* ret = execute_query(connection,query);
    MYSQL_ROW row=mysql_fetch_row(ret);
    if(atoi(row[0])==1)
    {
        mysql_free_result(ret);
        return true;
    }
    mysql_free_result(ret);
    return false;
}

//检测文件的上传状态仅仅针对当前文件所指向的对象

bool file_transfer_status(MYSQL* connection,const char* hash)
{
    char query[SQL_QUERY_SIZE]={0};
    sprintf(query,"select exists (select 1 from \
        directory_forest where file_hash = '%s' and transfer_success = 1 and file_type =2)",\
        hash);
    MYSQL_RES* ret =execute_query(connection,query);
    MYSQL_ROW row = mysql_fetch_row(ret);
    if(atoi(row[0])==1)
       {
       mysql_free_result(ret);
       return true;
       }
       mysql_free_result(ret);
       return false;
}


bool change_file_transfer(MYSQL* connection,const char* hash  )	// 断点续传失败，修改对应状态
{

    char query[SQL_QUERY_SIZE]={0};
    sprintf(query,"update directory_forest  set transfer_success = 1 \
            where file_hash ='%s' and transfer_success=0 and file_type=2",hash);
    bool ret =execute_non_query(connection,query);
    if(ret==false)
    {
        ERROR_LOG("执行文件上传状态修改出错");
        return false;
    }
    return true;
}
MYSQL_RES* utils_ls(MYSQL *connection,const char *user,const char *path)
{
    MYSQL_RES *result;

    char sql[1000] = { 0 };

    sprintf(sql,
            "select\
            file_name\
            from\
            directory_forest d\
            inner join  users u on d.belong_user = u.user_id\
             where username = '%s' and delete_sign = 0 and father_id =\
		                                    (select\
				                                file_id\
		                                     from\
				                                directory_forest d\
		                                     inner join  users u on d.belong_user = u.user_id\
		                                    where username = '%s' and file_path = '%s'and file_type = 1 and delete_sign = 0)"\
            ,user,user,path);

    if(mysql_query(connection,sql))
    {
        ERROR_LOG("%s\n",mysql_error(connection));
        return NULL;
    }

    result = mysql_store_result(connection);

    return result;   
}












